SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


--exec p_mstraderstartacc 2012,04,-99

alter        procedure p_mstraderstartacc (
  @nyear int,
  @nmonth int,
  @filid int
)  as  

  declare @traderid int,@newfilid int,@moneyid int
  declare @subjectid int
  declare @bmbala  numeric(18,2)
  declare @fbmbala numeric(18,2)
  declare @tmsum   numeric(18,2)
  declare @ftmsum  numeric(18,2)
  declare @local_tab table(traderid integer,
                           subjectid integer,
                           moneyid integer,
                           bmbala numeric(18,2),
                           fbmbala numeric(18,2))

begin
  set nocount on
  delete tft_mstrader where nyear=@nyear and nmonth=@nmonth  
  declare cur_mstrader cursor local for select * from @local_tab
  declare cur_fil cursor for select fi_filid from td_filiale where fi_filid=@filid or @filid =-99
  
  open cur_fil 
  fetch next from  cur_fil
  into  @newfilid;
  while @@fetch_status = 0 
  begin   
    delete from @local_tab
    insert into tft_mstrader (traderid,subjectid,moneyid,nyear,nmonth,filid ,tmsumdebit,tmsumcredit,tmsum,
      ftmsumdebit,ftmsumcredit,ftmsum)
    select d.traderid,d.subjectid,d.moneyid,@nyear,@nmonth,@newfilid ,
      sum(dcdebitamt) tmsumdebit,sum(dccreditamt) tmsumcredit, sum((dcdebitamt-dccreditamt)*sign(abs(m.formertype-2002))) tmsum,
      sum(debitamt) ftmsumdebit,sum(creditamt) ftmsumcredit, sum((debitamt-creditamt)*sign(abs(m.formertype-2002))) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and filid =@newfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.dotrader=1
      and d.traderid is not null
      and (d.voucherid<0 and (d.borderno=2 or d.borderno=3))
    group by  d.subjectid,d.traderid,d.moneyid

    insert into @local_tab
    select d.traderid,d.subjectid,d.moneyid,
      sum((s.isdebit*2-1)*(dcdebitamt+dccreditamt)) bmbala, 
      sum((s.isdebit*2-1)*(debitamt+creditamt)) fbmbala
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and filid =@newfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.dotrader=1 and (d.traderid is not null)
        and (d.voucherid<0 and d.borderno=1)
    group by  d.subjectid,d.traderid,d.moneyid

    open  cur_mstrader
    fetch next from  cur_mstrader
    into  @traderid,@subjectid,@moneyid,@bmbala,@fbmbala
     while @@fetch_status = 0
       begin
         update tft_mstrader set bmbala =@bmbala, fbmbala=@fbmbala
         where traderid = @traderid and subjectid = @subjectid and moneyid=@moneyid and filid=@newfilid
               and nyear=@nyear and nmonth=@nmonth
         fetch next from  cur_mstrader
         into  @traderid,@subjectid,@moneyid,@bmbala,@fbmbala
       end

    close  cur_mstrader
    delete from @local_tab

    insert into @local_tab
    select d.traderid,d.subjectid,d.moneyid,
      sum((s.isdebit*2-1)*(dcdebitamt)) tmsum,
      sum((s.isdebit*2-1)*(debitamt)) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and filid =@newfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.dotrader=1 and d.traderid is not null
        and (d.voucherid<0 and d.borderno=4)
    group by  d.subjectid,d.traderid,d.moneyid
    open  cur_mstrader
    fetch next from  cur_mstrader
    into  @traderid,@subjectid,@moneyid,@tmsum,@ftmsum
    while @@fetch_status = 0
      begin
        update tft_mstrader set tmsum = @tmsum,ftmsum = @ftmsum
        where traderid = @traderid and subjectid = @subjectid and moneyid=@moneyid and filid =@newfilid
              and nyear=@nyear and nmonth=@nmonth
        fetch next from  cur_mstrader
        into  @traderid,@subjectid,@moneyid,@tmsum,@ftmsum
      end
    close  cur_mstrader
    fetch next from  cur_fil
    into  @newfilid;
  end
  deallocate  cur_mstrader
  close cur_fil;
  deallocate  cur_fil;

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

